﻿using System;
using System.Collections.Generic;
using System.Collections;
using System.Linq;
using System.Text;
using System.IO;
using System.Data.SqlClient;
//using System.Data.SqlServerCe;
using System.Data;
using System.Data.OleDb;
//using ErikEJ.Sql;



namespace PAPI_Import
{
    partial class Program
    {
        static void OperateWorkMode7() // Sending a report with untreated Open POes: Delivery Date & Percentage problem. Runs on the 28th
        {
            SqlConnection SqlConn = new SqlConnection(sSqlConnectionString);

            string sClearSQL = @"   Select t1.PONumber,t1.Item,t2.First_Name,t2.Last_Name,t1.DeliveryDate,t1.Percentage_Update_Date
                                    FROM POes t1
                                    Inner join Papi_Users t2 ON t2.Requisitio=t1.Requisitioner
                                    Where t1.latest=1 AND t1.Status<99 
                                    AND ( DATEADD(day,7,GETDATE()) > t1.DeliveryDate OR (DATEADD(day,5,Percentage_Update_Date)<GETDATE()) AND t1.cost_elem='7721110')";

            SqlCommand SqlCmd = new SqlCommand(sClearSQL, SqlConn);
            SqlConn.Open();
            SqlDataReader dr = SqlCmd.ExecuteReader();

            int counter = 0;
            String st = "";
            
            while (dr.Read())
            {
                counter++;
                String sFirstName = dr.GetString(dr.GetOrdinal("First_Name"));
                String sLastName = dr.GetString(dr.GetOrdinal("Last_Name"));
                Int64 sPONUmber = dr.GetInt64(dr.GetOrdinal("PONumber"));
                int sItem = dr.GetInt32(dr.GetOrdinal("Item"));
                DateTime sDeliveryDate = dr.GetDateTime(dr.GetOrdinal("DeliveryDate"));
                DateTime dPercentage_Update_Date = dr.GetDateTime(dr.GetOrdinal("Percentage_Update_Date"));

                st += "PONumber: " + sPONUmber + ", Item: " + sItem + ", Owner: " + sFirstName + " " + sLastName + ", Delivery Date: " + sDeliveryDate.ToShortDateString() + ", Percentage date: " + dPercentage_Update_Date.ToShortDateString() + Environment.NewLine;
            }

            dr.Close();
            SqlConn.Close();


            if (counter != 0)
            {
                try
                {
                    PapiImportSendMail(MailType.WarningMail, getSpecialUsersEmails(UserType.Superuser),
                    "Hello Superusers, " + Environment.NewLine + Environment.NewLine +
                    "We are near the end of the month, and some items should be handled: " + Environment.NewLine + Environment.NewLine 
                   + st + Environment.NewLine + "Thank you," +  Environment.NewLine + "POMP System",false);

                    writeToLog("Sent " + counter + " reminder(s) to Superusers.");
                }
                catch (NoUsersException e)
                {
                    writeToLog("Error. No Superusers found in the system. Unable to send report.");
                }
            }
            else // (counter== 0)
            {
                writeToLog("Nothing to send.");
            }

            return;
        }
        

    }
}
